package sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
 * This class Handles all items sql procedurs
 * @author Ron Shenkerman
 * @author Ivan Onyshchenko
 */
public class Itemsql {

	
	/**
	 * get all item from db
	 * @return all items from db
	 * @throws SQLException
	 */
	public ArrayList<String>  getitemdb() throws SQLException
	{
		String[] st = null;
		ArrayList<String> arr=new ArrayList<String>();
		int i=0;
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			
			ResultSet rs= stmt.executeQuery("SELECT * FROM mtl.items ;");
				while (rs.next())
				{
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					
					
				}
				//System.out.print(arr);
			return arr;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
		
	}
	/**
	 * delete item from db
	 * @param id1 to delete
	 * @return 1 - ok ,0- error
	 * @throws SQLException
	 */
	public int  deleteitemdb(int id1) throws SQLException
	{
		
		
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			stmt.executeUpdate("delete from mtl.items  where iditems = '"+id1+"';");
			
			return 1;
		
		}
  
			catch (Exception ex )
			{
					ex.printStackTrace();
					return 0;
			}
			
		
		
	}
	/**
	 * add item to db
	 * @param ar - new item details
	 * @return 1-ok , 0-error
	 * @throws SQLException
	 */
	public int  additemdb(ArrayList<String> ar) throws SQLException
	{
		
	
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			System.out.println("add item");
			
			stmt.executeUpdate("insert into mtl.items(name,description,manufacturer) values('"+ar.get(0)+"','"+ar.get(1)+"','"+ar.get(2)+"');");
				
				
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
		
		
	}
	/**
	 * update item in the db
	 * @param ar - item detail to be updated
	 * @return 1-ok , 0-error
	 * @throws SQLException
	 */
	public int  updateitemdb(ArrayList<String> ar) throws SQLException
	{
		
	
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			System.out.println("update item");
			System.out.println(ar.get(0));
			System.out.println(ar.get(1));
			stmt.executeUpdate("update mtl.items set name = '"+ar.get(1)+"',description = '"+ar.get(2)+"',manufacturer = '"+ar.get(3)+"' where iditems = '"+ar.get(0)+"';");
						
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
		
		
	}
	/**
	 * get all item name and id from db
	 * @return item id and item name
	 */
	public ArrayList<String> getItemListDB() {
		String[] st = null;
		ArrayList<String> arr=new ArrayList<String>();
		try 
		{
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			ResultSet rs= stmt.executeQuery("SELECT mtl.items.iditems,name FROM mtl.items ;");
				while (rs.next())
				{
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
				}				
			return arr;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
	}
	/**
	 * get all inventory item from db
	 * @return all inventory items
	 * 		   and sum them
	 * @throws SQLException
	 */
	
	public ArrayList<String>  getinventory() throws SQLException
	{
		String[] st = null;
		ArrayList<String> arr=new ArrayList<String>();
		int i=0;
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			
			ResultSet rs= stmt.executeQuery("SELECT id,name,sum(quantity) cnt ,shelf FROM mtl.inventory a,mtl.items b WHERE a.id=b.iditems group by id ;");
				while (rs.next())
				{
					arr.add(rs.getString(1));
					arr.add(rs.getString(2));
					arr.add(rs.getString(3));
					arr.add(rs.getString(4));
					
					
					
				}
				//System.out.print(arr);
			return arr;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return null;
		
		
	}
	/**
	 * 
	 *the function attach item to shelf in the wherhouse
	 * @param ar - item to put on shelf detail
	 * @return 1 -ok , 0 error
	 * @throws SQLException
	 */
	public int putonshelfdb(ArrayList<String> ar) throws SQLException
	{
		String[] st = null;
		
		
		try 
		{

			
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			
			stmt.executeUpdate("UPDATE mtl.inventory SET shelf = '"+ar.get(1)+"' WHERE id = '"+ar.get(0)+"'");
				
				
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
		
		
	}
}
